package m.common.model.util;

import m.common.model.Model;
import m.system.db.DBManager;
import m.system.db.DataRow;
import m.system.db.DataSet;
import m.system.exception.MException;
import m.system.util.ArrayUtil;
import m.system.util.StringUtil;
import m.system.util.SystemUtil;

import java.sql.SQLException;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class FromQueryList implements IModelQuery {
	
	public static FromQueryList from(String alias,IModelQuery mTable,String... fieldNames) {
		return new FromQueryList(alias,mTable,false).addField(fieldNames);
	}
	/**
	 * 根据类查询
	 */
	public static FromQueryList from(String alias,Class<? extends Model> table,String... fieldNames) {
		return new FromQueryList(alias,ModelQueryList.instance(table, new String[] {}, null,null),false).addField(fieldNames);
	}
	/**
	 * 根据apiTable查询
	 */
	public static FromQueryList from(String alias,String name,String... fieldNames) throws Exception {
		return new FromQueryList(alias,TableQueryList.instance(name, new String[] {}, null,null),false).addField(fieldNames);
	}
	public static FromQueryList fromGroup(String alias,IModelQuery mTable,String... fieldNames) {
		return new FromQueryList(alias,mTable,true).addField(fieldNames);
	}
	/**
	 * 根据类查询
	 */
	public static FromQueryList fromGroup(String alias,Class<? extends Model> table,String... fieldNames) {
		return new FromQueryList(alias,ModelQueryList.instance(table, new String[] {}, null,null),true).addField(fieldNames);
	}
	/**
	 * 根据apiTable查询
	 */
	public static FromQueryList fromGroup(String alias,String name,String... fieldNames) throws Exception {
		return new FromQueryList(alias,TableQueryList.instance(name, new String[] {}, null,null),true).addField(fieldNames);
	}

	private FromQueryList(String alias,IModelQuery mTable,boolean isGroup) {
		this.mAlias=alias;
		this.mTable=mTable;
		this.fieldNames= new ArrayList<>();
		this.fields= new ArrayList<>();
		this.fieldToAliasMap= new LinkedHashMap<>();
		this.joinTables= new LinkedHashMap<>();
		this.joinTypes= new LinkedHashMap<>();
		this.joinOns= new LinkedHashMap<>();
		this.joinOnTrues= new LinkedHashMap<>();
		this.joinSqls= new LinkedHashMap<>();
		this.isGroup=isGroup;
		this.groups= new ArrayList<>();
		this.condition="";
		this.condParams= new ArrayList<>();
		this.having="";
		this.havParams= new ArrayList<>();
		this.unionList=new ArrayList<>();
	}
	private QueryParameter parameter;
	private QueryParameter sumParameter;
	private QueryParameter countParameter;
	private int num=0;
	private final String mAlias;
	private final IModelQuery mTable;
	private final List<String> fieldNames;//查询的属性
	private final List<String> fields;//翻译后
	private final Map<String,String> fieldToAliasMap;
	private final Map<String,IModelQuery> joinTables;
	private final Map<String,String> joinTypes;
	private final Map<String,String> joinOns;
	private final Map<String,String> joinOnTrues;//翻译后
	private final Map<String,QueryParameter> joinSqls;
	private final boolean isGroup;
	private final List<String> groups;//翻译后
	private String condition;
	private final List<Object> condParams;
	private String order;
	private String having;
	private final List<Object> havParams;
	private QueryPage page;
	private final List<IModelQuery> unionList;

	public FromQueryList setPage(QueryPage page) {
		this.page=page;
		return this;
	}
	@Override
	public FromQueryList addField(String... fieldName) {
		for(String f : fieldName) {
			if(!StringUtil.isSpace(f)&&!this.fieldNames.contains(f)) {
				this.fieldNames.add(f);
			}
		}
		return this;
	}
	@Deprecated
	@Override
	public IModelQuery addFieldExpression(String field, String expression) {
		throw new RuntimeException("该方法无效，无需设置。");
	}

	@Deprecated
	@Override
	public IModelQuery addCondition(QueryCondition... cond) {
		throw new RuntimeException("该方法无效，无需设置。");
	}

	@Deprecated
	@Override
	public IModelQuery addOrder(QueryOrder... order) {
		throw new RuntimeException("该方法无效，无需设置。");
	}

	@Override
	public FromQueryList unionAll(IModelQuery... querys) {
		this.unionList.addAll(Arrays.asList(querys));
		return this;
	}
	public FromQueryList addCondition(String condition) {
		if(StringUtil.isSpace(condition)) return this;
		this.condition +=" and "+ condition;
		return this;
	}
	public FromQueryList addCondition(String condition,Object... conds) {
		if(StringUtil.isSpace(condition)) return this;
		this.condition +=" and "+ condition;
		if(conds.length>0) {
			this.condParams.addAll(Arrays.asList(conds));
		}
		return this;
	}
	public FromQueryList addHaving(String having) {
		if(StringUtil.isSpace(having)) return this;
		this.having +=" and "+ having;
		return this;
	}
	public FromQueryList addHaving(String having,Object... havParams) {
		if(StringUtil.isSpace(having)) return this;
		this.having +=" and "+ having;
		if(havParams.length>0) {
			this.havParams.addAll(Arrays.asList(havParams));
		}
		return this;
	}
	public FromQueryList setOrder(String order) {
		this.order = order;
		return this;
	}
	public FromQueryList insertOrder(String order) {
		if(StringUtil.isSpace(order)) return this;
		if(StringUtil.isSpace(this.order)) {
			this.order=order;
		}else {
			this.order=order+","+this.order;
		}
		return this;
	}
	public FromQueryList addOrder(String order) {
		if(StringUtil.isSpace(order)) return this;
		if(StringUtil.isSpace(this.order)) {
			this.order=order;
		}else {
			this.order=this.order+","+order;
		}
		return this;
	}
	public FromQueryList leftJoin(String alias,String on,Class<? extends Model> table) {
		return leftJoin(alias,on,ModelQueryList.instance(table, new String[] {}, null,null));
	}
	public FromQueryList rightJoin(String alias,String on,Class<? extends Model> table) {
		return rightJoin(alias,on,ModelQueryList.instance(table, new String[] {}, null,null));
	}
	public FromQueryList join(String alias,String on,Class<? extends Model> table) {
		return join(alias,on,ModelQueryList.instance(table, new String[] {}, null,null));
	}
	public FromQueryList leftJoin(String alias,String on,IModelQuery table) {
		return join("left join",table,alias,on);
	}
	public FromQueryList rightJoin(String alias,String on,IModelQuery table) {
		return join("right join",table,alias,on);
	}
	public FromQueryList join(String alias,String on,IModelQuery table) {
		return join("join",table,alias,on);
	}
	public FromQueryList join(String type,IModelQuery table,String alias,String on) {
		joinTables.put(alias, table);
		joinTypes.put(alias, type);
		joinOns.put(alias, on);
		return this;
	}
	/**
	 * 获取sql查询的列别名, 通过fieldname
	 *  field
	 * 
	 */
	public String getAlias4Field(String field){
		String alias=this.fieldToAliasMap.get(field);
		if(null==alias) alias=this.fieldToAliasMap.get("#{"+field+"}");
		return alias;
	}
	public Map<String,String> getAsMap(){
		Map<String,String> map= new HashMap<>();
		for(String k : this.fieldToAliasMap.keySet()){
			if(k.indexOf("#{")==0){
				map.put(k,this.fieldToAliasMap.get(k));
			}
		}
		return map;
	}
	private boolean isGroupField(String field){
		return !field.toLowerCase().matches("^(min|max|count|sum|avg)\\(.*");
	}
	/**
	 * 初始化查询体所需的field字段
	 */
	private void setModelQueryFields(boolean flag) {
		for(String f : this.fieldNames) {
			String fa=findFieldName2Add(f,flag);
			if(flag) {
				if(!StringUtil.isSpace(fa)) {
					String n = "f_" + num++;
					if (fa.lastIndexOf(" ") >= 0) {
						n = fa.substring(fa.lastIndexOf(" ") + 1);
						fa = fa.substring(0, fa.lastIndexOf(" "));
						this.fieldToAliasMap.put(this.mAlias + "." + n, n);
					} else {
						this.fieldToAliasMap.put(f, n);
					}
					if (this.isGroup && isGroupField(fa)) {
						this.groups.add(fa);
					}
					String ff=fa + " " + n;
					if(!this.fields.contains(ff)){
						this.fields.add(ff);
					}
				}else if(f.matches("\\#\\{.+?\\}")){
					String tmp=f.substring(2,f.length()-1);
					this.fieldToAliasMap.put(tmp,tmp);
				}
			}
		}
		for(String k : this.joinOns.keySet()) {
			String f=this.joinOns.get(k);
			String fa=findFieldName2Add(f,flag);
			if(flag) {
				this.joinOnTrues.put(k, fa);
			}
		}
		if(!StringUtil.isSpace(this.condition)) {
			String fa=findFieldName2Add(condition,flag);
			if(flag) {
				this.condition=fa;
			}
		}
		if(!StringUtil.isSpace(this.order)) {
			String fa=findFieldName2Add(order,flag);
			if(flag) {
				this.order=fa;
			}
		}
		if(!StringUtil.isSpace(this.having)) {
			String fa=findFieldName2Add(having,flag);
			if(flag) {
				this.having=fa;
			}
		}
	}
	private String findFieldName2Add(String f,boolean flag) {
		Pattern pattern=Pattern.compile("\\#\\{.+?\\}");
		Matcher matcher=pattern.matcher(f);
		while(matcher.find()){
			String str=matcher.group();
			String ns=str.substring(2,str.length()-1);
			String fa=addModelQueryField(ns,flag);
			if(flag) {
				f=f.replace(str, fa);
			}
		}
		return f;
	}
	private String addModelQueryField(String fieldName,boolean flag) {
		int n=fieldName.indexOf(".");
		if(n>0) {
			String a=fieldName.substring(0,n);
			String fn=fieldName.substring(n+1);
			IModelQuery jt;
			if(a.equals(mAlias)) jt=mTable;
			else jt=joinTables.get(a);
			if(null!=jt) {
				if(flag) {
					try {
						int i=Integer.parseInt(fn);
						return a+".f_"+i;
					}catch(Exception ignored) {}
					return a+"."+jt.getAlias4Field(fn);
				}else if(jt instanceof FromQueryList){
					try {
						Integer.parseInt(fn);
						return "";
					}catch(Exception ignored) {}
					jt.addField("#{"+fn+"}");
				}else {
					jt.addField(fn);
				}
			}
		}
		return "";
	}
	public QueryParameter getQueryParameter() throws MException{
		if(null==this.parameter){
			setModelQueryFields(false);
			QueryParameter mp=mTable.getQueryParameter();
			for(String a : joinTables.keySet()) {
				joinSqls.put(a, joinTables.get(a).getQueryParameter());
			}
			setModelQueryFields(true);
			StringBuilder sql=new StringBuilder();
			StringBuilder sumSql=new StringBuilder("SELECT '' tmpField");
			for (String f : this.fields) {
				sql.append(",").append(f);
				if(!isGroupField(f)){
					sumSql.append(",").append(f);
				}
			}
			sql=new StringBuilder(sql.substring(1)).insert(0, "SELECT ");
			sql.append(" FROM (").append(mp.getSubSql()).append(") ").append(mAlias);
			sumSql.append(" FROM (").append(mp.getSubSql()).append(") ").append(mAlias);
			List<Object> ps = new ArrayList<>(mp.getValueList());
			List<Object> sumPs = new ArrayList<>(mp.getValueList());
			for(String a : joinTypes.keySet()) {
				sql.append(" ").append(joinTypes.get(a)).append(" (").append(joinSqls.get(a).getSubSql()).append(") ").append(a).append(" on ").append(joinOnTrues.get(a));
				sumSql.append(" ").append(joinTypes.get(a)).append(" (").append(joinSqls.get(a).getSubSql()).append(") ").append(a).append(" on ").append(joinOnTrues.get(a));
				ps.addAll(joinSqls.get(a).getValueList());
				sumPs.addAll(joinSqls.get(a).getValueList());
			}
			if(!StringUtil.isSpace(this.condition)) {
				sql.append(" WHERE 1=1 ").append(this.condition);
				sumSql.append(" WHERE 1=1 ").append(this.condition);
				ps.addAll(this.condParams);
				sumPs.addAll(this.condParams);
			}
			if(this.isGroup&& !groups.isEmpty()) {
				sql.append(" GROUP BY ").append(ArrayUtil.connection(groups.toArray(new String[] {}),","));
			}
			if(!StringUtil.isSpace(this.having)) {
				sql.append(" HAVING 1=1 ").append(this.having);
				sumSql.append(" HAVING 1=1 ").append(this.having);
				ps.addAll(this.havParams);
				sumPs.addAll(this.havParams);
			}
			if(!unionList.isEmpty()) {
				for (IModelQuery query : unionList) {
					QueryParameter qp = query.getQueryParameter();
					sql.append(" UNION ALL ").append(qp.getSubSql());
					sumSql.append(" UNION ALL ").append(qp.getSubSql());
					ps.addAll(qp.getValueList());
					sumPs.addAll(qp.getValueList());
				}
			}else {
				if (!StringUtil.isSpace(this.order)) {
					sql.append(" ORDER BY ").append(this.order);
				}
				if (null != this.page) {
					sql.append(" LIMIT ").append(this.page.getIndex()).append(",").append(this.page.getNum()).append(" ");
				}
			}
			this.parameter=new QueryParameter(sql.toString(), ps);
			this.sumParameter=new QueryParameter(sumSql.toString(), sumPs);
		}
		return this.parameter;
	}
	public QueryParameter getCountParameter() throws MException{
		if(null==this.countParameter){
			getQueryParameter();
			this.countParameter=new QueryParameter(this.parameter.getSql(), this.parameter.getValueList());
			this.countParameter.setSql("SELECT count(*) num FROM (" + this.countParameter.getSubSql() + ") a");
		}
		return this.countParameter;
	}
	public QueryParameter getSumParameter() throws MException {
		if(null==this.sumParameter){
			getQueryParameter();
		}
		return this.sumParameter;
	}
	public List<Map<String,Object>> toList() throws SQLException, MException{
		DataSet ds=DBManager.executeQuery(getQueryParameter());
		List<Map<String,Object>> list= new ArrayList<>();
		String as,key;
		for(DataRow row : ds.rows()) {
			Map<String,Object> map= new HashMap<>();
			for(int i=0;i<this.fieldNames.size();i++) {
				key=this.fieldNames.get(i);
				if(key.lastIndexOf(" ")>=0){
					as=key.substring(key.lastIndexOf(" ")+1);
				}else {
					as="f_"+i;
				}
				map.put(as,row.get(as));
			}
			list.add(map);
		}
		return list;
	}
	public int getCount() throws SQLException, MException {
		QueryParameter qp=getCountParameter();
		DataRow ds=DBManager.queryFirstRow(qp.getSql(), qp.getValueList().toArray(new Object[]{}));
		if(null!=ds){
			return ds.get(Long.class, "num").intValue();
		}
		return 0;
	}
	

	public static void main(String[] a) throws Exception {
		//InitListener.initDB("");
		//AdminLogin admin=ModelQueryList.getModel(AdminLogin.class, "1", new String[] {"*"});
//		FromQueryList fq=FromQueryList.from("aa",
//			FromQueryList.fromGroup("aa",
//				AdminLogin.class,"aa.adminGroup.oid f1","count(#{aa.oid}) cn_oid"
//			)
//		).leftJoin("dd", "#{dd.adminGroup.oid}=#{aa.f1}",
//			AdminLogin.class
//		)
//		.addField("#{dd.realname} realname","#{dd.lastLoginTime}","#{dd.adminGroup.oid}","#{aa.cn_oid}");
////		fq.addCondition("#{aa.aa.cn_oid}>1");
////		fq.setOrder("#{dd.adminGroup.oid} desc,#{aa.aa.cn_oid} desc");
//		QueryParameter qp=fq.getQueryParameter();
//		SystemUtil.println(qp.getSql());
		//SystemUtil.println(ObjectUtil.toString(admin));

//		FromQueryList fq=FromQueryList.from("a0",
//				FromQueryList.fromGroup("a",
//					AdminLogin.class,"#{a.adminGroup.oid} f0","count(#{a.oid}) f1"
//				)
//			).leftJoin("a", "#{a.adminGroup.oid}=#{a0.f0}",AdminLogin.class
//			)
//			.addField("#{a.adminGroup.nameNum} name","#{a0.f1} na");
//		fq.addCondition("#{aa.aa.cn_oid}>1");
//		fq.setOrder("#{dd.adminGroup.oid} desc,#{aa.aa.cn_oid} desc");
//		QueryParameter qp=fq.getQueryParameter();
//		SystemUtil.println(fq.toList());
//		String f="faMin()aamax()ss";
//		SystemUtil.println(f);
//		Pattern pattern=Pattern.compile("(min|max|count|sum|avg)\\(.*?\\)",Pattern.CASE_INSENSITIVE);
//		Matcher matcher=pattern.matcher(f);
//		while(matcher.find()){
//			String str=matcher.group();
//			SystemUtil.println(str);
//		}
		FromQueryList fq=FromQueryList.from("a",
			new FromQuerySql().append("SELECT oid,image_type,img_path FROM os_image_info")
		);
		fq.addField("#{a.oid}","#{a.image_type}","#{a.img_path}");
		fq.addCondition("#{a.oid}='1'");
		fq.getQueryParameter();
		SystemUtil.println(fq.toList());

	}

}
